package com.aimee.dao;

import com.aimee.entity.*;
import com.aimee.util.Dbutil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public class ExamPaperDAO {

    public static void main(String[] args) {
        ExamPaperDAO e = new ExamPaperDAO();
        ExamPaper examPaper = new ExamPaper();
        try {
            e.showExams();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    /**
     * 得到班级的总数 count
     *
     * @return
     * @throws SQLException
     */
    public Long countClass() throws SQLException {
        String sql = "SELECT COUNT(*) FROM classes;";
        Connection conn = Dbutil.getConn();
        QueryRunner runner = new QueryRunner();
        Long count = runner.query(conn, sql, new ScalarHandler<Long>());

        conn.close();
        return count;
    }

    /**
     * 展示所有班级专业院系
     *
     * @return
     * @throws SQLException
     */
    public List showClassDept() throws SQLException {
        String sql = "SELECT c.class_id,a.academy_name,m.major_name,c.class_name,c.class_pnum from classes c INNER JOIN major m ON c.major_id = m.major_id INNER JOIN academy a ON m.major_id = a.academy_id;";
        Connection conn = Dbutil.getConn();
        QueryRunner runner = new QueryRunner();
        List<ClassMajorDeptShow> list = runner.query(conn, sql, new BeanListHandler<ClassMajorDeptShow>(ClassMajorDeptShow.class));
        conn.close();
        return list;
    }


    /**
     * 展示所有试卷列表
     *
     * @return
     * @throws SQLException
     */
    public List showExams() throws SQLException {
        String sql = "SELECT e.exampaper_id, e.exampaper_name,c.course_name,u.user_name,e.create_time,e.exampaper_desc FROM exampaper e INNER JOIN course c ON e.course_id = c.course_id INNER JOIN `user` u ON e.user_id = u.user_id";

        Connection conn = Dbutil.getConn();
        QueryRunner runner = new QueryRunner();
        List<ExamPaperShow> list = runner.query(conn, sql, new BeanListHandler<ExamPaperShow>(ExamPaperShow.class));

        conn.close();
        return list;
    }
//    我的试卷***********************************************************************************

    //    ******************************分页查询****************************

    /**
     * 首页分页查询所有试卷
     *
     * @param user_id
     * @return
     * @throws SQLException
     */
    public List allmyexam(int user_id) throws SQLException {
//        String sql = "SELECT * FROM `exampaper` where user_id=? limit ?,10";, int page
        String sql = "SELECT * FROM `exampaper` where user_id=?";
        Connection conn = Dbutil.getConn();
//        int t = (page - 1) * 10;
        QueryRunner runner = new QueryRunner();
        List list = runner.query(conn, sql, new BeanListHandler<ExamPaper>(ExamPaper.class), user_id);
        conn.close();
        return list;
    }

    // 查询所有试卷数
    public long countexampaper(int user_id) throws SQLException {
        String sql = "SELECT COUNT(*) FROM `exampaper`  where user_id=?";
        Connection conn = Dbutil.getConn();
        QueryRunner qr = new QueryRunner();
        long count = qr.query(conn, sql, new ScalarHandler<Long>(), user_id);
        System.out.println(count);
        conn.close();
        return count;
    }

    //按照所属科目分类分页查询试卷/**/
    public List findexampaperBycouseid(int course_id, int user_id) throws SQLException {
//        String sql = "SELECT * FROM `exampaper` WHERE course_id=? and  user_id=? limit ?,10";, int page
        String sql = "SELECT * FROM `exampaper` WHERE course_id=? and  user_id=?";
        Connection conn = Dbutil.getConn();
//        int t = (page - 1) * 10;
        QueryRunner runner = new QueryRunner();
        List list = runner.query(conn, sql, new BeanListHandler<ExamPaper>(ExamPaper.class), course_id, user_id);
        conn.close();
        return list;
    }

    // 按照所属科目分类查询所有试卷数
    public long countexampaperBycouseid(int course_id, int user_id) throws SQLException {
        String sql = "SELECT COUNT(*) FROM `exampaper` WHERE course_id=? and  user_id=?";
        Connection conn = Dbutil.getConn();
        QueryRunner qr = new QueryRunner();
        long count = qr.query(conn, sql, new ScalarHandler<Long>(), course_id, user_id);
        System.out.println(count);
        conn.close();
        return count;
    }

    //******************************************************************************************
    //删除试卷
    public int delexampaper(int exampaper_id) throws SQLException {
        String sql = "DELETE FROM `exampaper` WHERE exampaper_id=?";
        Connection conn = Dbutil.getConn();
        QueryRunner runner = new QueryRunner();
        int n = runner.update(conn, sql, exampaper_id);
        conn.close();
        return 0;
    }

    //    共享试卷********exampaper_type为1表示私有试卷，为2表示共享试卷***************************************************************************
    //通过试卷类型首页分页查询来查看所有共享试卷
    public List allshareexam() throws SQLException {
//        String sql = "SELECT * FROM `exampaper` WHERE exampaper_type=2 limit ?,10";int page
        String sql = "SELECT * FROM `exampaper` WHERE exampaper_type=2";
        Connection conn = Dbutil.getConn();
//        int t = (page - 1) * 10;
        QueryRunner runner = new QueryRunner();
        List list = runner.query(conn, sql, new BeanListHandler<ExamPaper>(ExamPaper.class));
        conn.close();
        return list;
    }

    // 按照试卷类型分类查询所有试卷数
    public long countByexampapertype() throws SQLException {
        String sql = "SELECT COUNT(*) FROM `exampaper` WHERE exampaper_type=2";
        Connection conn = Dbutil.getConn();
        QueryRunner qr = new QueryRunner();
        long count = qr.query(conn, sql, new ScalarHandler<Long>());
        System.out.println(count);
        conn.close();
        return count;
    }

    //按照条件查询共享试卷信息
    public List shareexambyid(String exampaper_name, int course_id, int user_id) throws SQLException {
//        String sql = "SELECT * FROM `exampaper` WHERE exampaper_name=? AND course_id=? AND user_id=? limit ?,10";, int page
        String sql = "SELECT * FROM `exampaper` WHERE exampaper_name=? AND course_id=? AND user_id=?";
        Connection conn = Dbutil.getConn();
//        int t = (page - 1) * 10;
        QueryRunner runner = new QueryRunner();
        List list = runner.query(conn, sql, new BeanListHandler<ExamPaper>(ExamPaper.class), exampaper_name, course_id, user_id);
        conn.close();
        return list;
    }

    // 按照条件查询所有试卷数
    public long countByid(String exampaper_name, int course_id, int user_id) throws SQLException {
        String sql = "SELECT COUNT(*) FROM `exampaper` WHERE exampaper_name=? AND course_id=? AND user_id=?";
        Connection conn = Dbutil.getConn();
        QueryRunner qr = new QueryRunner();
        long count = qr.query(conn, sql, new ScalarHandler<Long>(), exampaper_name, course_id, user_id);
        System.out.println(count);
        conn.close();
        return count;
    }

    //查询所有题库 显示为下拉框数据
    public List findallcourse() throws SQLException {
        String sql = "SELECT * FROM `course`";
        Connection conn = Dbutil.getConn();
        QueryRunner runner = new QueryRunner();
        List list = runner.query(conn, sql, new BeanListHandler<Course>(Course.class));
        conn.close();
        return list;
    }

    //*******************************创建试卷*******************************************\
    //创建试卷 int user_id,
    public int createexampaper(ExamPaper examPaper) throws SQLException {
        String sql = "INSERT INTO `exampaper` VALUE(NULL,?,?,?,?,?,NOW(),?)";
        Connection conn = Dbutil.getConn();
        QueryRunner runner = new QueryRunner();
        int n = 0;
        Object param[] = new Object[6];
        param[0] = examPaper.getUser_id();
        param[1] = examPaper.getCourse_id();
        param[2] = examPaper.getExampaper_name();
        param[3] = examPaper.getExampaper_desc();
        param[4] = examPaper.getExampaper_score();
        param[5] = examPaper.getExampaper_type();
        n = runner.update(conn, sql, param);
        conn.close();
        return n;
    }

    //查询所有的试题 分页
    public List findallquestion() throws SQLException {
//        String sql="SELECT * FROM `questions` limit ?,10"; int page
        String sql = "SELECT * FROM `questions`";
//        int t = (page - 1) * 10;
        Connection conn = Dbutil.getConn();
        QueryRunner runner = new QueryRunner();
//        List list = runner.query(conn, sql, new BeanListHandler<Questions>(Questions.class),t);
        List list = runner.query(conn, sql, new BeanListHandler<Questions>(Questions.class));
        conn.close();
        return list;
    }

    // 查询所有试题数
    public long countquestion() throws SQLException {
        String sql = "SELECT COUNT(*) FROM `questions`";
        Connection conn = Dbutil.getConn();
        QueryRunner qr = new QueryRunner();
        long count = qr.query(conn, sql, new ScalarHandler<Long>());
        System.out.println(count);
        conn.close();
        return count;
    }

    //按条件查询试题 分页
    public List findquestionbyid(String question_type, int course_id) throws SQLException {
//        String sql="SELECT * FROM `questions` WHERE question_type=? AND course_id=? limit ?,10"; ,int page
        String sql = "SELECT * FROM `questions` WHERE question_type=? AND course_id=?";
//        int t = (page - 1) * 10;
        Connection conn = Dbutil.getConn();
        QueryRunner runner = new QueryRunner();
//        List list = runner.query(conn, sql, new BeanListHandler<Questions>(Questions.class),question_type,course_id,t);
        List list = runner.query(conn, sql, new BeanListHandler<Questions>(Questions.class), question_type, course_id);
        conn.close();
        return list;
    }

    // 按照所属科目和问题种类分类查询所有试ti数
    public long countquestionBycouseid(String question_type, int course_id) throws SQLException {
        String sql = "SELECT COUNT(*) FROM `questions` WHERE course_id=? and  question_type=?";
        Connection conn = Dbutil.getConn();
        QueryRunner qr = new QueryRunner();
        long count = qr.query(conn, sql, new ScalarHandler<Long>(), question_type, course_id);
        System.out.println(count);
        conn.close();
        return count;
    }

    //*******************************修改试卷*******************************************\
    //修改试卷
    public int updateexampaper(int exampaper_id, ExamPaper examPaper) throws SQLException {
        String sql = "UPDATE `exampaper` SET course_id=?,exampaper_name=?,exampaper_desc=?,exampaper_score=?,create_time=NOW(),exampaper_type=? WHERE exampaper_id=?";
        Connection conn = Dbutil.getConn();
        QueryRunner runner = new QueryRunner();
        int n = 0;
        Object param[] = new Object[6];
        param[0] = examPaper.getCourse_id();
        param[1] = examPaper.getExampaper_name();
        param[2] = examPaper.getExampaper_desc();
        param[3] = examPaper.getExampaper_score();
        param[4] = examPaper.getExampaper_type();
        param[5] = exampaper_id;
        n = runner.update(conn, sql, param);
        conn.close();
        return n;
    }

    //查询试卷id by试卷名  试卷名唯一
    public List findexamidbyname(String exampaper_name) throws SQLException {
        String sql = "SELECT * FROM `exampaper` WHERE exampaper_name=?";
        Connection conn = Dbutil.getConn();
        QueryRunner runner = new QueryRunner();
//        long count = qr.query(conn, sql, new ScalarHandler<Long>(),exampaper_name);
        List<ExamPaper> list = runner.query(conn, sql, new BeanListHandler<ExamPaper>(ExamPaper.class), exampaper_name);
        System.out.println(list);
        conn.close();
        return list;
    }
}
